with TAB_BARSCAN_SEND as(
    select
         to_date(SCAN_TIME) as date_time,
           scan_type,
          SCAN_USER_CODE as user_code,
           max(scan_user) as user_name,
           count(1) as send_sum
    from jms_dwd.dwd_tab_barscan_sitesend_base_dt
    where dt>=date_sub('{{ execution_date | cst_ds }}',3)
    and dt<='{{ execution_date | cst_ds }}'
    and network_type=4
    and to_date(SCAN_TIME)='{{ execution_date | cst_ds }}'
    group by to_date(SCAN_TIME),SCAN_USER_CODE,scan_type
),
     TAB_BARSCAN_TAKING as (
         select
                to_date(SCAN_TIME) as date_time,
                scan_type,
                deliver_user_code as user_code,
                max(deliver_user) as user_name,
                count(1) as taking_sum
         from jms_dwd.dwd_tab_barscan_taking_base_dt
         where dt>=date_sub('{{ execution_date | cst_ds }}',3)
         and dt<='{{ execution_date | cst_ds }}'
         and to_date(SCAN_TIME)='{{ execution_date | cst_ds }}'
         group by to_date(SCAN_TIME),deliver_user_code,scan_type
     )
insert overwrite table jms_dm.dm_rabbit_person_taking_sed_sum_dt partition(dt)    
-- 兔兔来了业务员揽收派件量汇总
select date_time                        --业务日期
       ,user_code                       --业务员code
       ,max(user_name)  as user_name    --业务员名字
       ,sum(send_sum)   as send_sum     --发件总量
       ,sum(taking_sum) as  taking_sum  --揽收总量
       ,date_time as dt  --分区日期
from (
     select
          date_time
         ,scan_type
         ,user_code
         ,user_name
         ,send_sum
         ,0 as taking_sum
     from TAB_BARSCAN_SEND
     union
     select
          date_time
         ,scan_type
         ,user_code
         ,user_name
         , 0 as send_sum
         ,taking_sum
      from TAB_BARSCAN_TAKING)tmp
group by date_time,user_code
distribute by 10
;
